﻿using DoNetTool.Common.Core.Extensions;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;

namespace DoNetTool.Common.Core
{
    /// <summary>
    /// 数据库访问工具类
    /// </summary>
    public class SqlTool
    {
        
        public class FieldDef
        {
            public string Name;
            public string Type;
            public int Len;
            public string SQLName;

            public void SetValue(string sFieldDetail)
            {
                var stmp = sFieldDetail.Split(',');
                Name = stmp[0].Trim();//.ToLower()
                Type = stmp[1].Trim();
                Len = System.Convert.ToInt32(stmp[2].Trim());
                if (stmp.Length == 4)
                    SQLName = stmp[3].Trim();
                else
                    SQLName = Name;
            }

            public static List<FieldDef> Split(string sFieldDetails)
            {
                var sTmp = sFieldDetails.Split(new[] { StringUtil.StringNewLine }, StringSplitOptions.RemoveEmptyEntries);
                var oList = new List<FieldDef>(sTmp.Length);
                foreach (var detail in sTmp)
                {
                    var f = new FieldDef();
                    f.SetValue(detail);
                    oList.Add(f);
                }
                return oList;
            }
        }


        public static string GetValueOnForm(NameValueCollection fmd, string sKey)
        {
            var sValues = fmd.GetValues(sKey);
            if (sValues == null)
                return string.Empty;
            else
                return sValues[0];
        }

        /// <summary>
        /// 创建SQL 查询语句
        /// </summary>
        /// <param name="sWhere"></param>
        /// <param name="sFields">设定查询字段名和类型   
        /// 格式每组回车分隔，组中,号分隔，组中元素0表示字段名，元素2表示字段类型（str---字符串,int--数值,date--时间），元素3表示长度。</param>
        /// <param name="form"></param>
        /// <returns></returns>
        public static string CreateSQLWhere(string sWhere, string sFields, NameValueCollection form)
        {
            var sSQL = new System.Text.StringBuilder(1024);
            sSQL.Append(sWhere);
            var oFields = FieldDef.Split(sFields);
            var sFormKeys = form.AllKeys;
            var sOperList = "=,<>,>,<,>=,<=,!=,!<,!>".Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

            foreach (var oField in oFields)
            {
                if (oField.Type != "null")
                {
                    if (oField.Type == "date")
                    {
                        string sBeginKey, sEndKey;
                        string sBeginValue, sEndValue;
                        sBeginKey = $"{oField.Name}_Begin";
                        sEndKey = $"{oField.Name}_End";

                        sBeginValue = GetValueOnForm(form, sBeginKey);
                        sEndValue = GetValueOnForm(form, sEndKey);
                        if ((!string.IsNullOrEmpty(sBeginValue)) & (!string.IsNullOrEmpty(sEndValue)))
                        {
                            sBeginValue = StringUtil.CheckInputText(sBeginValue, oField.Len);
                            sEndValue = StringUtil.CheckInputText(sEndValue, oField.Len);

                            if (sBeginValue.IsDate() & sEndValue.IsDate())
                            {
                                if (sSQL.Length > 0)
                                    sSQL.Append(" And ");
                                sSQL.Append($"{oField.SQLName} between '{DateTime.Parse(sBeginValue).ToDateTimeStr()}' and '{DateTime.Parse(sEndValue).ToDateTimeStr()}'");
                            }
                        }
                    }
                    else
                    {
                        var iKeyIndex = sFormKeys.FindTextIndex(oField.Name);
                        if (iKeyIndex > -1)
                        {
                            var sOpt = GetValueOnForm(form, $"{oField.Name}_oper");
                            if (string.IsNullOrEmpty(sOpt))
                                sOpt = "=";

                            string oValue;


                            if (sSQL.Length > 0)
                                sSQL.Append(" And ");


                            oValue = form.GetValues(iKeyIndex)[0];
                            if (!("str,pwdList".IndexOf(oField.Type) >= 0))
                                oValue = StringUtil.CheckInputText(oValue, oField.Len);


                            switch (oField.Type)
                            {
                                case "str":
                                    {
                                        if (sOperList.FindTextIndex(sOpt) > -1)
                                        {
                                            oValue = StringUtil.CheckInputText(oValue, oField.Len);
                                            sSQL.Append($"{oField.SQLName} {sOpt} '{oValue}'");
                                        }

                                        if (sOpt.IsSame("Like"))
                                        {
                                            oValue = StringUtil.CheckInputText(oValue, oField.Len);
                                            sSQL.Append($"{oField.SQLName} Like '%{oValue}%'");
                                        }

                                        if (sOpt.IsSame("List"))
                                        {
                                            oValue = StringUtil.CheckStringList(oValue, oField.Len, false);
                                            sSQL.Append($"{oField.SQLName} in ( {oValue} )");
                                        }

                                        break;
                                    }

                                case "pwd":
                                    {
                                        if (oValue.IsNum())
                                        {
                                            if (sOperList.FindTextIndex(sOpt) > -1)
                                                sSQL.Append($"{oField.SQLName} {sOpt} '{oValue}'");

                                            if (sOpt.IsSame("Like"))
                                                sSQL.Append($"{oField.SQLName} Like '%{oValue}%'");
                                        }

                                        break;
                                    }

                                case "pwdList":
                                    {
                                        oValue = StringUtil.CheckStringList(oValue, oField.Len, false);
                                        if (!string.IsNullOrEmpty(oValue))
                                        {
                                            if (oField.Name.EndsWith("s") & oField.Name == oField.SQLName)
                                            {
                                                oField.Name = oField.Name.Substring(0, oField.Name.Length - 1);
                                                oField.SQLName = oField.Name;
                                            }


                                            if (sOpt == "=")
                                                sSQL.Append($"{oField.SQLName} in ( {oValue} )");
                                            else if (sOpt == "!=")
                                                sSQL.Append($"{oField.SQLName} not in ( {oValue} )");
                                        }

                                        break;
                                    }

                                case "int":
                                    {
                                        if (oValue.IsNum())
                                        {
                                            if (sOpt.IsSame("Range"))
                                            {
                                                string sEndKey;
                                                string sEndValue;
                                                sEndKey = $"{oField.Name}_End";

                                                sEndValue = GetValueOnForm(form, sEndKey);
                                                sEndValue = StringUtil.CheckInputText(sEndValue, oField.Len);

                                                if (sEndValue.IsNum())
                                                    sSQL.Append($"{oField.SQLName} between {oValue} And {sEndValue}");
                                            }
                                            else if (sOperList.FindTextIndex(sOpt) > -1)
                                                sSQL.Append($"{oField.SQLName} {sOpt} {oValue}");
                                        }

                                        break;
                                    }

                                case "IDList":
                                    {
                                        oValue = StringUtil.CheckIDListNoDelete(oValue);
                                        if (oField.Name.EndsWith("s") & oField.Name == oField.SQLName)
                                        {
                                            oField.Name = oField.Name.Substring(0, oField.Name.Length - 1);
                                            oField.SQLName = oField.Name;
                                        }


                                        if (sOpt == "=")
                                            sSQL.Append($"{oField.SQLName} in ( {oValue} )");
                                        else if (sOpt == "!=")
                                            sSQL.Append($"{oField.SQLName} not in ( {oValue} )");
                                        break;
                                    }
                                case "StrList":
                                    {
                                        oValue = StringUtil.CheckStrListNoDelete(oValue);
                                        if (oField.Name.EndsWith("s") & oField.Name == oField.SQLName)
                                        {
                                            oField.Name = oField.Name.Substring(0, oField.Name.Length - 1);
                                            oField.SQLName = oField.Name;
                                        }


                                        if (sOpt == "=")
                                            sSQL.Append($"{oField.SQLName} in ( {oValue} )");
                                        else if (sOpt == "!=")
                                            sSQL.Append($"{oField.SQLName} not in ( {oValue} )");
                                        break;
                                    }
                            }
                        }
                    }
                }
            }

            return sSQL.ToString();
        }


    }
}
